﻿#if !NETSTANDARD2_1
using NetTopologySuite.Algorithm;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace Dapper.Sharding
{
    internal class SapHanaTableManager : ITableManager
    {
        public SapHanaTableManager(string name, IDatabase database) : base(name, database)
        {

        }

        public override void AddColumn(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("DEFAULT 0"))
            {
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?) )
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            AddColumn(name, dbType, comment);
        }

        public override void AddColumn(string name, string columnType, string comment = null)
        {
            var sql = $"ALTER TABLE {Name.ToUpper()} ADD ({name.ToUpper()} {columnType})";
            DataBase.Execute(sql);
            if (!string.IsNullOrEmpty(comment))
            {
                CommentColumn(name, comment);
            }
        }

        public override async Task AddColumnAsync(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("DEFAULT 0"))
            {
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?))
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            var sql = $"ALTER TABLE {Name.ToUpper()} ADD ({name.ToUpper()} {columnType})";
            await DataBase.ExecuteAsync(sql);
            if (!string.IsNullOrEmpty(comment))
            {
                CommentColumn(name, comment);
            }
        }

        public override void CommentColumn(string name, string comment)
        {
            var sql = $"COMMENT ON COLUMN {Name.ToUpper()}.{name.ToUpper()} IS '{comment}'";
            DataBase.Execute(sql);
        }

        public override void CreateIndex(string name, string columns, IndexType indexType)
        {
            if (indexType == IndexType.Unique)
            {
                DataBase.Execute($"CREATE UNIQUE INDEX {Name.ToUpper()}_{name} ON {Name.ToUpper()} ({columns})");
            }
            else
            {
                DataBase.Execute($"CREATE INDEX {Name.ToUpper()}_{name} ON {Name.ToUpper()} ({columns})");
            }
        }

        public override void DropColumn(string name)
        {
            var sql = $"ALTER TABLE {Name.ToUpper()} DROP ({name.ToUpper()})";
            DataBase.Execute(sql);
        }

        public override async Task DropColumnAsync(string name)
        {
            var sql = $"ALTER TABLE {Name.ToUpper()} DROP ({name.ToUpper()})";
            await DataBase.ExecuteAsync(sql);
        }

        public override void DropIndex(string name)
        {
            var sql = $"DROP INDEX {Name.ToUpper()}.{name}";
            DataBase.Execute(sql);
        }

        public override List<ColumnEntity> GetColumnEntityList(TableEntity tb = null, bool firstCharToUpper = false)
        {
            //GENERATION_TYPE=ALWAYS AS IDENTITY
            tb ??= new TableEntity();
            var list = new List<ColumnEntity>();

            var sql = $"SELECT COLUMN_NAME,DATA_TYPE_NAME,LENGTH,SCALE,COMMENTS,GENERATION_TYPE FROM TABLE_COLUMNS WHERE SCHEMA_NAME='{DataBase.Name.ToUpper()}' AND TABLE_NAME='{Name.ToUpper()}' ORDER BY POSITION";
            var columnList = DataBase.Query(sql);
            foreach (var item in columnList)
            {
                var model = new ColumnEntity();

                var generation_type = (string)item.GENERATION_TYPE;
                if (!string.IsNullOrEmpty(generation_type))
                {
                    if (generation_type.Contains("IDENTITY"))
                    {
                        model.IsIdentity = true;
                    }
                }

                if (firstCharToUpper)
                {
                    model.Name = ((string)item.COLUMN_NAME).FirstCharToUpper(); //列名
                }
                else
                {
                    model.Name = (string)item.COLUMN_NAME;
                }

                model.DbType = item.DATA_TYPE_NAME;//数据类型

                var map = DbCsharpTypeMap.SapHanaMap.FirstOrDefault(f => f.DbType == model.DbType);
                if (map != null)
                {
                    model.CsStringType = map.CsStringType;
                    model.CsType = map.CsType;
                }
                else
                {
                    model.CsStringType = "object";
                    model.CsType = typeof(object);
                }

                model.Length = item.LENGTH;
                if (item.SCALE != null)
                {
                    model.Scale = item.SCALE;
                }

                if (model.CsType == typeof(decimal))
                {
                    if (model.Scale <= 0 || !model.Scale.ToString().EndsWith("0"))
                    {
                        model.Length = Convert.ToDouble($"{model.Length}.{model.Scale}");
                        model.Scale = 0;
                    }
                }

                if (model.DbType == "NCLOB")
                {
                    model.Length = -1;
                    model.Scale = 0;
                }

                model.DbLength = model.Length.ToString();

                model.Comment = item.COMMENTS; //说明
                list.Add(model);
            }

            return list;
        }

        public override async Task<List<ColumnEntity>> GetColumnEntityListAsync(TableEntity tb = null, bool firstCharToUpper = false)
        {
            tb ??= new TableEntity();
            var list = new List<ColumnEntity>();

            var sql = $"SELECT COLUMN_NAME,DATA_TYPE_NAME,LENGTH,SCALE,COMMENTS,GENERATION_TYPE FROM TABLE_COLUMNS WHERE SCHEMA_NAME='{DataBase.Name.ToUpper()}' AND TABLE_NAME='{Name.ToUpper()}' ORDER BY POSITION";
            var columnList = await DataBase.QueryAsync(sql);
            foreach (var item in columnList)
            {
                var model = new ColumnEntity();

                if (firstCharToUpper)
                {
                    model.Name = ((string)item.COLUMN_NAME).FirstCharToUpper(); //列名
                }
                else
                {
                    model.Name = (string)item.COLUMN_NAME;
                }

                model.DbType = item.DATA_TYPE_NAME;//数据类型

                var map = DbCsharpTypeMap.SapHanaMap.FirstOrDefault(f => f.DbType == model.DbType);
                if (map != null)
                {
                    model.CsStringType = map.CsStringType;
                    model.CsType = map.CsType;
                }
                else
                {
                    model.CsStringType = "object";
                    model.CsType = typeof(object);
                }

                model.Length = item.LENGTH;
                if (item.SCALE != null)
                {
                    model.Scale = item.SCALE;
                }

                if (model.CsType == typeof(decimal))
                {
                    if (model.Scale <= 0 || !model.Scale.ToString().EndsWith("0"))
                    {
                        model.Length = Convert.ToDouble($"{model.Length}.{model.Scale}");
                        model.Scale = 0;
                    }
                }

                if (model.DbType == "NCLOB")
                {
                    model.Length = -1;
                    model.Scale = 0;
                }

                model.DbLength = model.Length.ToString();

                model.Comment = item.COMMENTS; //说明
                list.Add(model);
            }

            return list;
        }

        public override List<IndexEntity> GetIndexEntityList()
        {
            var sql = $"SELECT INDEX_NAME,CONSTRAINT,COLUMN_NAME FROM INDEX_COLUMNS WHERE SCHEMA_NAME='{DataBase.Name.ToUpper()}' AND TABLE_NAME='{Name.ToUpper()}'";
            var indexList = DataBase.Query(sql);
            var list = new List<IndexEntity>();
            foreach (var item in indexList)
            {
                var model = new IndexEntity();
                model.Name = item.INDEX_NAME;
                model.Columns = item.COLUMN_NAME;
                var cons = (string)item.CONSTRAINT;
                if (string.IsNullOrEmpty(cons))
                {
                    model.Type = IndexType.Normal;
                }
                else if (cons.Contains("PRIMARY KEY"))
                {
                    model.Type = IndexType.PrimaryKey;
                }
                else if (cons.Contains("UNIQUE"))
                {
                    model.Type = IndexType.Unique;
                }

                list.Add(model);
            }
            return list;
        }

        public override void ModifyColumn(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("DEFAULT 0"))
            {
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset)  && t != typeof(DateTime?) && t != typeof(DateTimeOffset?))
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            ModifyColumn(name, dbType, comment);
        }

        public override void ModifyColumn(string name, string columnType, string comment = null)
        {
            var sql = $"ALTER TABLE {Name.ToUpper()} ALTER({name.ToUpper()} {columnType})";
            DataBase.Execute(sql);
            if (!string.IsNullOrEmpty(comment))
            {
                CommentColumn(name, comment);
            }
        }

        public override async Task ModifyColumnAsync(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("DEFAULT 0"))
            {
#if CORE6
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateOnly) && t != typeof(TimeOnly) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?) && t != typeof(DateOnly?) && t != typeof(TimeOnly?))
#else
                if (t != null &&  t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?))
#endif
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            await ModifyColumnAsync(name, dbType, comment);

        }

        public override async Task ModifyColumnAsync(string name, string columnType, string comment = null)
        {
            var sql = $"ALTER TABLE {Name.ToUpper()} ALTER({name.ToUpper()} {columnType})";
            await DataBase.ExecuteAsync(sql);
            if (!string.IsNullOrEmpty(comment))
            {
                CommentColumn(name, comment);
            }
        }

        public override void ReNameColumn(string name, string newName, Type t = null, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var sql = $"RENAME COLUMN {Name.ToUpper()}.{name.ToUpper()} TO {newName.ToUpper()}";
        }
    }
}
#endif